package jdbc.dbutils.dao.iml;


import jdbc.dbutils.dao.EmpDao;
import jdbc.dbutils.domain.Emp;
import jdbc.dbutils.untils.DataSourceUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.List;
/*
BeanListHandler 返回list集合
BeanHandler  返回单个对象
ScalarHandler  返回单个值
ArrayHandler 返回数组
ColumnListHandler 返回单列集合
 */

public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> findAll() {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp", new BeanListHandler<>(Emp.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    @Override
    public Emp findByEmpno(Integer empno) {

        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp where empno=?", new BeanHandler<>(Emp.class),empno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



    @Override
    public void add(Emp emp) {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        Object[] params={emp.getEmpno(),emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno()};
        try {
            qr.update("insert into emp values(?,?,?,?,?,?,?,?)",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Override
    public void update(Emp emp) {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        Object[] params={emp.getEname(),emp.getJob(),emp.getMgr(),emp.getHiredate(),emp.getSal(),emp.getComm(),emp.getDeptno(),emp.getEmpno()};
        try {
            qr.update("update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(Integer empno) {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            qr.update("delete from emp where empno=?",empno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public long getCount() {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select count(*) from emp", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Object[] findByEmpno2(Integer empno) {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp where empno=?",new ArrayHandler(),empno);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<String> findNames(){
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select ename from emp", new ColumnListHandler<String>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
